﻿using System.Collections.Generic;
using System.Data.SQLite;

public class Highscore
{
    private const string DataSource = "highscores.db";
    private readonly SQLiteConnection connection;

    public int Level;
    public string PlayerName;
    public int Score;
    public double Time;

    public Highscore()
    {
        this.connection = new SQLiteConnection { ConnectionString = "data source=" + DataSource };

        CreateTableIfNotExists();
    }

    private void CreateTableIfNotExists()
    {
        this.connection.Open();
        SQLiteCommand cmd = new SQLiteCommand(this.connection);

        cmd.CommandText =
            "CREATE TABLE IF NOT EXISTS scores (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, playerName VARCHAR(100) NOT NULL, score INTEGER NOT NULL, time DOUBLE PRECISION NOT NULL, level INTEGER NOT NULL);";
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        this.connection.Close();
    }

    public void InsertNewEntry()
    {
        this.connection.Open();
        SQLiteCommand cmd = new SQLiteCommand(this.connection);

        cmd.CommandText =
            string.Format(
                "INSERT INTO scores (id, playerName, score, time, level) VALUES(NULL, '{0}', '{1}', '{2}', '{3}')",
                this.PlayerName, this.Score, this.Time, Level);
        cmd.ExecuteNonQuery();

        cmd.Dispose();
        this.connection.Close();
    }

    public List<Highscore> ReceiveData()
    {
        this.connection.Open();
        SQLiteCommand cmd = new SQLiteCommand(this.connection);

        cmd.CommandText = string.Format("SELECT id, playerName, score, time, level FROM scores ORDER BY score DESC");

        SQLiteDataReader reader = cmd.ExecuteReader();

        List<Highscore> hs = new List<Highscore>();

        while (reader.Read())
        {
            string name = (string)reader[1];
            int score = int.Parse(reader[2].ToString());
            double time = double.Parse((string)reader[3]);
            int level = int.Parse(reader[4].ToString());

            hs.Add(new Highscore
                {
                    PlayerName = name,
                    Score = score,
                    Time = time,
                    Level = level
                });
        }

        reader.Close();
        reader.Dispose();

        cmd.Dispose();
        this.connection.Close();

        return hs;
    }

    public void CloseConnection()
    {
        this.connection.Close();
        this.connection.Dispose();
    }
}